package com.daniel.database;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.daniel.memo.*;

public class Database {

	private static final String DATABASE_NAME = "android_memo";
	private static final int DATABASE_VERSION = 1;	
	
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;

	/** ==================================================== CREATE TABLES ============================================================ **/
	//CREATE TABLE Memo (memoId INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(200), memo VARCHAR(2000), type INTEGER, date DATE, todoId INTEGER, taskId INTEGER);
	private static final String CREATE_MEMO_TABLE = " create table " + Memo.MEMO_TABLE_NAME + " ("
																	+ Memo.MEMO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
																	+ Memo.TITLE + " VARCHAR(200) not null,"
																	+ Memo.MEMO + " VARCHAR(2000),"
																	+ Memo.TYPE + " INTEGER,"
																	+ Memo.DATE + " DATE not null,"
																	+ Memo.TODO_ID + " INTEGER ," 
																	+ Memo.TASK_ID + " INTEGER "
																	+ ")";

	
	//CREATE TABLE Todo (todoId INTEGER PRIMARY KEY AUTOINCREMENT, memoId INTEGER, numOfTasks INTERGER);
	private static final String CREATE_TODO_TABLE = " create table " + Todo.TODO_TABLE_NAME + "("
																	+ Todo.TODO_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
																	+ Todo.MEMO_ID + " INTEGER not null,"
																	+ Todo.NUM_OF_TASKS + " INTERGER);";
	
	
	//CREATE TABLE Task (taskId INTEGER PRIMARY KEY AUTOINCREMENT, memoId INTEGER, todoId INTEGER, 
	//aramDateTime DATETIME, isAramOn BOOL, priority INTEGER, isCompleted BOOL);
	private static final String CREATE_TASK_TABLE = " create table " + Task.TASK_TABLE_NAME + "("
																	+ Task.TASK_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
																	+ Task.MEMO_ID + " INTEGER,"
																	+ Task.TODO_ID + " INTEGER,"
																	+ Task.ARAM_TIME_DATE + " DATETIME,"
																	+ Task.IS_ARAM_ON + " BOOL,"
																	+ Task.PRIORITY + " INTEGER,"
																	+ Task.IS_COMPLETED + " BOOL);";
	
	
	/** ==================================================== CREATE TRIGGERS ============================================================ **/
	public static final String DELETE_MEMO = "Delete_Memo";
	private static final String CREATE_DELETE_MEMO_TRIGGER = "CREATE TRIGGER " + DELETE_MEMO + " DELETE ON "+ Memo.MEMO_TABLE_NAME
																+ " BEGIN "
																+ " DELETE FROM " + Todo.TODO_TABLE_NAME + " WHERE " + Todo.TODO_TABLE_NAME + "." + Todo.MEMO_ID + " = " + Memo.MEMO_TABLE_NAME + "." + Memo.MEMO_ID + "; "
																+ " DELETE FROM " + Task.TASK_TABLE_NAME + " WHERE " + Task.TASK_TABLE_NAME + "." + Task.MEMO_ID + " = " + Memo.MEMO_TABLE_NAME + "." + Memo.MEMO_ID + "; "
																+ " END; ";
	
	public static final String DELETE_TODO = "Delete_Todo";
	private static final String CREATE_DELETE_TODO_TRIGGER = "CREATE TRIGGER " + DELETE_TODO + " DELETE ON " + Todo.TODO_TABLE_NAME 
															+ " BEGIN "
															+ " DELETE FROM " + Task.TASK_TABLE_NAME + " WHERE " + Task.TASK_TABLE_NAME + "." + Task.TODO_ID + " = " +  Todo.TODO_TABLE_NAME + "." + Todo.TODO_ID + ";" 
															+ " END; ";
	
	public static final String DELETE_TASK = "Delete_Task";
	private static final String CREATE_DELETE_TASK_TRIGGER = "CREATE TRIGGER " + DELETE_TASK + " DELETE ON " + Task.TASK_TABLE_NAME
													+ " BEGIN "
													+ " UPDATE " + Todo.TODO_TABLE_NAME + " SET " + Todo.NUM_OF_TASKS + " = " + Todo.NUM_OF_TASKS + " - 1 " + " WHERE " + Todo.TODO_TABLE_NAME + "." +Todo.MEMO_ID + " = " + Task.TASK_TABLE_NAME + "." + Task.MEMO_ID + ";"  
													+ " END ";
	
	public static final String INSERT_TASK = "Insert_Task";
	private static final String CREATE_INSERT_TASK = "CREATE TRIGGER " + INSERT_TASK + " INSERT ON " + Task.TASK_TABLE_NAME
						+ " BEGIN "
						+ "UPDATE " + Todo.TODO_TABLE_NAME + " SET " + Todo.NUM_OF_TASKS + " = " + Todo.TODO_TABLE_NAME + " + 1 " + " WHERE " + Todo.TODO_TABLE_NAME + "." +Todo.MEMO_ID + " = " + Task.TASK_TABLE_NAME + "." + Task.MEMO_ID + ";"
						+ " END ";
	
	
	//Constructor
	public Database(Context ctx) {
		mCtx= ctx;
	}

	// open database
	public Database open() throws android.database.SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}
	
	public void close() {
		mDbHelper.close();
	}
	
	
	//DatabaseHelper
	private static class DatabaseHelper extends SQLiteOpenHelper {
		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			//Create tables
			db.execSQL(CREATE_MEMO_TABLE);
			db.execSQL(CREATE_TASK_TABLE);
			db.execSQL(CREATE_TODO_TABLE);
			
			//Create Trigger
//			db.execSQL(CREATE_DELETE_MEMO_TRIGGER);
//			db.execSQL(CREATE_DELETE_TODO_TRIGGER);
//			db.execSQL(CREATE_DELETE_TASK_TRIGGER);
//			db.execSQL(CREATE_INSERT_TASK);
			
//			Log.d("Memo Table", CREATE_MEMO_TABLE);
//			Log.d("Todo Table", CREATE_TODO_TABLE);
//			Log.d("Task Table", CREATE_TASK_TABLE);
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		}
		
	}	
	
	
	public String getCurrentDate() {
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    	Calendar cal = Calendar.getInstance();
    	String date = dateFormat.format(cal.getTime());
    	
    	return date;
	}
	
	/***** MEMO *****/
	public long insertMemo(String title, String memo, int type, String date) {
		long rowId = -1;
		
		ContentValues initialValues = new ContentValues();
		initialValues.put(Memo.TITLE, title);
		initialValues.put(Memo.MEMO, memo);
		initialValues.put(Memo.TYPE, type);
		initialValues.put(Memo.DATE, date);
			
		try {
			rowId = mDb.insertOrThrow(Memo.MEMO_TABLE_NAME, null, initialValues);
		} catch(SQLException e) {
			Log.e("SQLException : ",e.toString());
		}
		
		Log.d("database rowId", "" + rowId);
		
		return rowId;
	}
	
	public void updateTitle(int memoId, String title) {
		String whereClause = "";
		whereClause += Memo.MEMO_ID + " = " + memoId;
		
		ContentValues initialValues = new ContentValues();
		initialValues.put(Memo.TITLE, title);
		
		mDb.update(Memo.MEMO_TABLE_NAME, initialValues, whereClause, null);
	}
	
	public void updateMemo(int rowId, String title, String memo, int type, String date) {
		
		String whereClause = "";
		whereClause += Memo.MEMO_ID + " = " + rowId;
		
		ContentValues initialValues = new ContentValues();
		initialValues.put(Memo.TITLE, title);
		initialValues.put(Memo.MEMO, memo);
		initialValues.put(Memo.TYPE, type);
		initialValues.put(Memo.DATE, date);

		mDb.update(Memo.MEMO_TABLE_NAME, initialValues, whereClause, null);
	}
	
	//extract title and memo data from memo
	public Cursor fetchMemoById(int memoId) throws SQLException {
		String [] columns = new String[] {Memo.TITLE, Memo.DATE, Memo.MEMO};
		Cursor c =  mDb.query(Memo.MEMO_TABLE_NAME, columns, Memo.MEMO_ID + " = " + memoId, null, null, null, null);
		return c;
	}

	public void deleteByMemoId(int memoId) {
		String whereClause = Memo.MEMO_ID + " = " + memoId;
//		Log.d("whereClause",whereClause);
		mDb.delete(Memo.MEMO_TABLE_NAME, whereClause, null);
	}

	//extract title and memo data from memo
	public Cursor fetchAllMemoForMain() throws SQLException {
		String [] columns = new String[] {Memo.MEMO_ID, Memo.TITLE, Memo.TYPE};
		Cursor c =  mDb.query(Memo.MEMO_TABLE_NAME, columns, null, null, null, null, null);
		return c;
	}
	
	/***** END MEMO *****/
	
	
	/***** TODO *****/
	public long insertTodo(String title) {
		long memoRowId = 0, todoRowId = 0;

		ContentValues memoInitValue = new ContentValues();
		memoInitValue.put(Memo.TITLE, title);
		memoInitValue.put(Memo.TYPE, Memo.TODO_TYPE);
		String date = getCurrentDate();
		memoInitValue.put(Memo.DATE, date);
		try {
			memoRowId = mDb.insertOrThrow(Memo.MEMO_TABLE_NAME, null, memoInitValue);
		} catch(SQLException e) {
			Log.e("SQLException : ",e.toString());
		}
		
		ContentValues todoInitValue = new ContentValues(); 
		todoInitValue.put(Todo.NUM_OF_TASKS, 0);
		todoInitValue.put(Todo.MEMO_ID, memoRowId);
		try {
			todoRowId = mDb.insertOrThrow(Todo.TODO_TABLE_NAME, null, todoInitValue);
		} catch(SQLException e) {
			Log.e("SQLException : ",e.toString());
		}
		
		return todoRowId;
	}
	
	public Cursor fetchTodoByMemoId(int memoId) {
		Cursor cursor = null;
		String whereClause = "";
		whereClause += Todo.MEMO_ID + " = " + memoId;
//		Log.d("whereClause",whereClause);
		String [] columns = new String [] {Todo.TODO_ID, Todo.NUM_OF_TASKS};
		cursor = mDb.query(Todo.TODO_TABLE_NAME, columns, whereClause, null, null, null, null);

		return cursor;
	}

	public void deleteTodoById(int memoId) {
		String whereClause = "";
		whereClause += Todo.MEMO_ID + " = " + memoId;
		mDb.delete(Todo.TODO_TABLE_NAME, whereClause, null);
	}
	/*
	public void updateTodoTitle(int todoId, String newTitle) {
		
		String whereClause = "";
		whereClause += Todo.TODO_ID + " = " + todoId;
			
		ContentValues initialValues = new ContentValues();
		initialValues.put(Todo., isCompleted);

//		mDb.update(Task.TASK_TABLE_NAME, initialValues, whereClause, null);
	}
	*/
	/***** END TODO *****/
	
	
	/***** Task *****/
	public Cursor fetchTasksWithTodoId(int todoId) {
		Cursor cursor = null;
		String whereClause = "";
		whereClause += Todo.TODO_ID + " = " + todoId;

		Log.d("whereClasue",whereClause);
		

		String [] columns = new String [] {Task.TASK_ID, Task.MEMO_ID, Task.TODO_ID, 
				Task.ARAM_TIME_DATE, Task.IS_ARAM_ON, 
				Task.PRIORITY, Task.IS_COMPLETED};
				
		cursor =  mDb.query(Task.TASK_TABLE_NAME, columns, whereClause, null, null, null, null);
		
		return cursor;
	}
	
	public Cursor fetchTasksByTaskId(int taskId) {
		Cursor cursor = null;
		String where = "";
		where += Task.TASK_ID + " = " + taskId;

		String [] columns = new String [] {Task.TASK_ID, Task.MEMO_ID, Task.TODO_ID, Task.ARAM_TIME_DATE, Task.IS_ARAM_ON, 
				Task.PRIORITY, Task.IS_COMPLETED ,Task.TODO_ID};
		cursor =  mDb.query(Task.TASK_TABLE_NAME, columns, where, null, null, null, null);
		
		
		
		return cursor;
	}
	
	public long insertTask(String taskTitle, String taskMemo, String currentDate, int todoId, String date, boolean isAramOn, int priority, boolean isCompleted) {
		long taskRowId = 0;
		
		int memoId = (int)insertMemo(taskTitle, taskMemo, Memo.TASK_TYPE, currentDate);
		
		ContentValues taskInitValue = new ContentValues();
		taskInitValue.put(Task.MEMO_ID, memoId);
		taskInitValue.put(Task.TODO_ID, todoId);
		taskInitValue.put(Task.ARAM_TIME_DATE, date);
		taskInitValue.put(Task.IS_ARAM_ON, isAramOn);
		taskInitValue.put(Task.PRIORITY, priority);
		taskInitValue.put(Task.IS_COMPLETED, isCompleted);
		try {
			taskRowId = mDb.insertOrThrow(Task.TASK_TABLE_NAME, null, taskInitValue);
		} catch (SQLException e) {
			Log.e("SQLException : ",e.toString());
		}
		
		return taskRowId;
	}

	public void updateTask(int memoId, int taskId, String taskTitle, String taskMemo, String currentDate, 
			int todoId, String date, boolean isAramOn, int priority, boolean isCompleted) {
		
		updateMemo(memoId, taskTitle, taskMemo, Task.TASK_TYPE, currentDate);
		
		ContentValues taskInitValue = new ContentValues();
		taskInitValue.put(Task.MEMO_ID, memoId);
		taskInitValue.put(Task.TODO_ID, todoId);
		taskInitValue.put(Task.ARAM_TIME_DATE, date);
		taskInitValue.put(Task.IS_ARAM_ON, isAramOn);
		taskInitValue.put(Task.PRIORITY, priority);
		taskInitValue.put(Task.IS_COMPLETED, isCompleted);
		
		String whereClause = "";
		whereClause += Task.TASK_ID + " = " + taskId;
		
		mDb.update(Task.TASK_TABLE_NAME, taskInitValue, whereClause, null);
	}
	
	public void updateIsCompleted(int taskId, boolean isCompleted) {
		String whereClause = "";
		whereClause += Task.TASK_ID + " = " + taskId;
			
		ContentValues initialValues = new ContentValues();
		initialValues.put(Task.IS_COMPLETED, isCompleted);

		mDb.update(Task.TASK_TABLE_NAME, initialValues, whereClause, null);
	}
	
	public void deleteTaskById(int taskId, int memoId) {
		deleteByMemoId(memoId);
		String whereClause = "";
		whereClause += Task.TASK_ID + " = " + taskId;
		mDb.delete(Task.TASK_TABLE_NAME, whereClause, null);
	}
	
	/***** End Task *****/
}
